﻿Imports Entidades
Imports System.Data.OleDb

Public Class AccesoDatos
    Dim prestamo As Prestamo
    Private conexion As New OleDbConnection
    Public Sub New()
        conexion.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=cardsonal.accdb"

    End Sub
    Private Function AbrirConexion() As String
        Try
            If conexion.State = ConnectionState.Closed Then
                conexion.Open()
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return ""
    End Function
    Public ReadOnly Property Cartas As ObjectModel.ReadOnlyCollection(Of Carta)
        Get
            Dim cards As New List(Of Carta)
            If AbrirConexion() = "" Then  ' Si error al acceder a la bd devuelvo la lista vacía
                Dim sql As String = "Select Id, Titulo, Tipo, Expansion, Baraja,Ejemplares From Carta"

                Dim consultaPrestamos As New OleDbCommand(sql, conexion)

                Dim dataReader As OleDbDataReader = consultaPrestamos.ExecuteReader()

                If Not dataReader.HasRows Then
                    CerrarConexion()
                    Return cards.AsReadOnly
                End If
                While dataReader.Read()
                    cards.Add(New Carta(dataReader.Item("Id"), dataReader.Item("Titulo"), dataReader("Tipo"), dataReader("Expansion"), dataReader("Baraja"), dataReader("Ejemplares")))
                End While
            End If
            CerrarConexion()
            Return cards.AsReadOnly

        End Get
    End Property
    Public ReadOnly Property Duelistas As ObjectModel.ReadOnlyCollection(Of Duelista)
        Get
            Dim duel As New List(Of Duelista)
            If AbrirConexion() = "" Then  ' Si error al acceder a la bd devuelvo la lista vacía
                Dim sql As String = "Select Id, Nombre, Telefono  From Duelista"

                Dim consultaDuelistas As New OleDbCommand(sql, conexion)

                Dim dataReader As OleDbDataReader = consultaDuelistas.ExecuteReader()

                If Not dataReader.HasRows Then
                    CerrarConexion()
                    Return duel.AsReadOnly
                End If
                While dataReader.Read()
                    duel.Add(New Duelista(dataReader.Item("Id"), dataReader.Item("Nombre"), dataReader("Telefono")))
                End While
            End If
            CerrarConexion()
            Return duel.AsReadOnly

        End Get
    End Property
    Public ReadOnly Property Prestamos As ObjectModel.ReadOnlyCollection(Of Prestamo)
        Get
            Dim presta As New List(Of Prestamo)
            If AbrirConexion() = "" Then  ' Si error al acceder a la bd devuelvo la lista vacía
                Dim sql As String = "Select Id, Dueño, FechaPrestamo, FechaDevolucion From Prestamo"

                Dim consultaPrestamos As New OleDbCommand(sql, conexion)

                Dim dataReader As OleDbDataReader = consultaPrestamos.ExecuteReader()

                If Not dataReader.HasRows Then
                    CerrarConexion()
                    Return presta.AsReadOnly
                End If
                While dataReader.Read()
                    presta.Add(New Prestamo(dataReader.Item("Id"), dataReader.Item("Dueño"), dataReader("FechaPrestamo"), dataReader("FechaDevolucion")))
                End While
            End If
            CerrarConexion()
            Return presta.AsReadOnly

        End Get
    End Property
    Public ReadOnly Property Barajas As ObjectModel.ReadOnlyCollection(Of Baraja)
        Get
            Dim decks As New List(Of Baraja)
            If AbrirConexion() = "" Then  ' Si error al acceder a la bd devuelvo la lista vacía
                Dim sql As String = "Select Id, Nombre From Baraja"

                Dim consultaPrestamos As New OleDbCommand(sql, conexion)

                Dim dataReader As OleDbDataReader = consultaPrestamos.ExecuteReader()

                If Not dataReader.HasRows Then
                    CerrarConexion()
                    Return decks.AsReadOnly
                End If
                While dataReader.Read()
                    decks.Add(New Baraja(dataReader.Item("Id"), dataReader.Item("Nombre")))
                End While
            End If
            CerrarConexion()
            Return decks.AsReadOnly

        End Get
    End Property
    Public Function DuelistaPorPrestamo(ByVal prestamo As Prestamo) As ObjectModel.ReadOnlyCollection(Of Duelista)
        Dim nombre = prestamo.Nombre
        Dim duelistas As New List(Of Duelista)
        If AbrirConexion() <> "" Then Return duelistas.AsReadOnly

        Dim sql As String = "Select * Duelista From  where nombre =@nombre"
        Dim conexionDuelistaCarta As New OleDbCommand(sql, conexion)
        conexionDuelistaCarta.Parameters.AddWithValue("@nombre", nombre)
        Dim dataReader As OleDbDataReader = conexionDuelistaCarta.ExecuteReader()

        If dataReader.HasRows Then
            While dataReader.Read()
                If Not IsNothing(dataReader.Item("Id")) Then

                    duelistas.Add(New Duelista(dataReader.Item("Id"), dataReader.Item("Nombre"), dataReader("Telefono")))
                End If
            End While
        End If

        CerrarConexion()
        Return duelistas.AsReadOnly
    End Function
    Private Function CerrarConexion() As String
        Try
            If conexion.State = ConnectionState.Open Then conexion.Close()
        Catch ex As Exception
            Return ex.Message
        End Try
        Return ""
    End Function
End Class